package com.api.gbhg.heyulei.dao.impl;

import cn.hutool.core.util.StrUtil;
import com.api.gbhg.heyulei.dao.ReimbursementQueryDao;
import org.apache.commons.lang.StringUtils;
import weaver.conn.RecordSet;

import java.util.Map;

/**
 * @author hyl
 * @version 1.0
 * @date 2021/4/17 14:15
 */
public class ReimbursementQueryDaoImpl implements ReimbursementQueryDao {

    public static String sql;

    /**
     * 查询表名
     */
    @Override
    public String tableName(String requestId) {
        RecordSet rs = new RecordSet();
        rs.executeQuery("select  l.tablename  from workflow_requestbase r,workflow_base s,workflow_bill l where\n" +
                "r.workflowid = s.id and s.formid = l.id and  r.requestid =" + requestId);
        rs.next();
        return rs.getStringNoTrim("tablename");
    }


    /**
     * 查询主表内容
     * @param tablefname 表名
     * @param requestId
     * @param fieldName
     * @return
     */
    @Override
    public RecordSet getPrimayTable(String tablefname, String requestId, String fieldName) {
        StringBuffer sb = new StringBuffer();
        sb.append("select * from " + tablefname + " m,hrmResource e  where  m.requestId in(" + requestId + ") and m."+ fieldName +" = e.id ");

        RecordSet rs = new RecordSet();
        rs.executeQuery(sb.toString());
        return rs;
    }

    /**
     * 查询报销流程requestid
     * @param map
     * @return
     */
    @Override
    public RecordSet getRequestId(Map<String, Object> map) {
        int pageNum = Integer.parseInt(map.get("pageNum").toString());
        int pageSize = Integer.parseInt(map.get("pageSize").toString());
        pageNum = (pageNum - 1) * pageSize;


        //属于报销流程的workflwoid
        String workflowid = "129,133,138,139,141,143,145,147,150,151,152,157,156,144,140,136,134";
        StringBuffer sb = new StringBuffer();
        sb.append("select se.requestid,ce.lastname,se.createdate,se.workflowid from " +
                "workflow_requestbase se,hrmResource ce " +
                " where se.workflowid in("+workflowid+")  and se.creater = ce.id and se.status = '会计核单'");

        //年份
        if (StringUtils.isNotEmpty(map.get("yearTime").toString())) {
            sb.append(" AND YEAR(se.createdate) = " + map.get("yearTime").toString());
        }
        //单位id
        if (StringUtils.isNotEmpty(map.get("department").toString())) {
            sb.append(" AND ce.subcompanyid1 = " + map.get("department").toString());
        }
        //经办人id
        if (map.get("agent").toString().length() > 1) {
            sb.append(" AND ce.lastname like '%" + map.get("agent").toString() + "%'");
        }
        //开始时间和结束时间
        if (StringUtils.isNotEmpty(map.get("startTime").toString()) && StringUtils.isNotBlank(map.get("endTime").toString())) {
            sb.append(" AND se.createdate BETWEEN '" + map.get("startTime").toString() + "' AND '" + map.get("endTime").toString() + "'");

        }

        sql = sb.toString();

        sb.append("  order by se.requestid desc offset " + pageNum + " rows fetch next " + pageSize + " rows only ");

        RecordSet rs = new RecordSet();
        rs.executeQuery(sb.toString());

        return  rs;
    }

    /**
     * 总数
     */
    @Override
    public Integer getcount(){
        RecordSet rs = new RecordSet();
        rs.executeQuery(sql);
        return  rs.getArray().size();
    }
}
